using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Configuration;


namespace MOVIE_THEATER
{
    
    public class SqlDataAccessHelper
    {       
        
        #region ConnectionString
        //private static String connectionString;      
        public static String ConnectionString
        {
            get
            {
                //return @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\QLBVCB.mdf;Integrated Security=True;User Instance=True";
                //return ConfigurationManager.ConnectionStrings["QLBVCB"].ConnectionString;
                return ReadConnectionString("DataString.xml");
                //return @"Data Source=localhost;Initial Catalog=QLBVCB;Integrated Security=True";
            }
        }
        public static String ReadConnectionString(string file)
        {
            try
            
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(file);
                XmlElement root = doc.DocumentElement;
                String connectionString = root.InnerText;
                return connectionString;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion
        #region ExecuteQuery
        public static DataTable ExecuteQuery(String query)
        {
           
            DataTable table = new DataTable();
            try
            {
                SqlConnection connect = new SqlConnection(ConnectionString);
                connect.Open();
                try
                {
                    SqlCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(table);
                }
                    
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }
        #endregion
        #region ExecuteNoneQuery
        public static int ExecuteNoneQuery(String query)
        {
            int n;
            try
            {
                
                SqlConnection connect = new SqlConnection(ConnectionString);
                connect.Open();
                try
                {
                    SqlCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    n = command.ExecuteNonQuery(); 
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return n;
        }
        #endregion

        #region transaction
        

        public static List<DataTable> executeTransaction(String[] queryArr, bool[] isNoneQueryArr)
        {
            List<DataTable> result = new List<DataTable>();
            SqlConnection connect = new SqlConnection(ConnectionString);
            connect.Open();

            SqlTransaction trans = connect.BeginTransaction();
            SqlCommand com = new SqlCommand();
            com = connect.CreateCommand();
            com.Transaction = trans;
            try
            {
                for (int i = 0; i < queryArr.Length; i++)
                {
                    if (isNoneQueryArr[i])
                    {
                        com.CommandType = CommandType.Text;
                        com.CommandText = queryArr[i];
                        com.ExecuteNonQuery();
                    }
                    else
                    {
                        com.CommandType = CommandType.Text;
                        com.CommandText = queryArr[i];
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = com;
                        DataTable table = new DataTable();
                        adapter.Fill(table);
                        result.Add(table);
                    }
                }
                trans.Commit();
                connect.Close();
                return result;
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw e;
            }
        }
        #endregion

        public static void executeStoreProcedure(string procName, SqlParameter[] sqlParam)
        {

            SqlConnection connect = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand com = new SqlCommand();
                com.CommandTimeout = 2000;
                connect.Open();
                com.Connection = connect;
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = procName;
                for (int i = 0; i < sqlParam.Length; i++)
                {
                    com.Parameters.Add(sqlParam[i]);
                }
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }

            finally
            {
                connect.Close();
            }
        }

        public static List<DataTable> ThucThiStoreTraVeBang(string procName, SqlParameter[] sqlParam)
        {
            List<DataTable> result = new List<DataTable>();
            SqlConnection connect = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand com = new SqlCommand();
                com.CommandTimeout = 2000;
                connect.Open();
                com.Connection = connect;
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = procName;
                for (int i = 0; i < sqlParam.Length; i++)
                {
                    com.Parameters.Add(sqlParam[i]);
                }
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = com;
                DataTable table1 = new DataTable();
                DataTable table2 = new DataTable();
                result.Add(table1);
                result.Add(table2);
                connect.Close();
                return result;            
            }
            catch (SqlException ex)
            {
                connect.Close();
                throw ex;
            }            
        }

    }
}
